﻿CREATE DATABASE ProjectWeb
GO
USE ProjectWeb
GO
CREATE TABLE ACCOUNT(
	idacc int identity(1,1),
	username nvarchar(30) UNIQUE,
	[address] nvarchar(50),
	fullname nvarchar(50),
	email varchar(30),
	[password] nvarchar(30),
	dob date,
	cmt int,
	phone int,
	[role] varchar(30),
	CONSTRAINT pk_ACCOUNT PRIMARY KEY(idacc)
)

GO
CREATE TABLE CATEGORY(
	idcate int identity(1,1),
	category nvarchar(50),
	CONSTRAINT pk_CATEGORY PRIMARY KEY(idcate)
)

GO
CREATE TABLE SOURCE_LINK(
	idlink int identity(1,1),
	category int,
	keyword varchar(100),
	link varchar(200),
	[status] varchar(20),
	CONSTRAINT pk_LINK PRIMARY KEY(idlink),
	FOREIGN KEY(category) REFERENCES CATEGORY(idcate)
)

GO
CREATE TABLE PROVINCE
(
	idprovince int identity(1,1),
	province nvarchar(50),
	CONSTRAINT pk_pro PRIMARY KEY(idprovince)
)

GO
CREATE TABLE LOCATION(
	idlocal int identity(1,1),
	localname nvarchar(100),
	category int,
	province int,
	district nvarchar(100),
	addressDetail nvarchar(100),
	phone int,
	website varchar(50),
	descript varchar(5000),
	
	CONSTRAINT pk_LOCATION PRIMARY KEY(idlocal),
	FOREIGN KEY(category) REFERENCES CATEGORY(idcate),
	FOREIGN KEY(province) REFERENCES PROVINCE(idprovince)
)
SELECT * FROM LOCATION

GO
CREATE TABLE USER_LOCATION
(
	iduser int identity(1,1),
	email varchar(50),
	encode varchar(20),
	location int,
	CONSTRAINT pk_user PRIMARY KEY(iduser),
	FOREIGN KEY(location) REFERENCES LOCATION(idlocal)
)

GO
CREATE TABLE EDITOR_LOCATION
(
	idcontent int identity(1,1),
	location int,
	account int,
	CONSTRAINT pk_editor PRIMARY KEY(idcontent),
	FOREIGN KEY(location) REFERENCES LOCATION(idlocal),
	FOREIGN KEY(account) REFERENCES ACCOUNT(idacc)
)

GO
SELECT idlocal,localname,category,province,addressDetail,descript
FROM (
	SELECT idlocal,localname,b.category,c.province,addressDetail,descript
	FROM LOCATION a,CATEGORY b,PROVINCE c
	WHERE a.category = b.idcate AND a.province = c.idprovince )AS A
WHERE localname LIKE '%city%'


GO
CREATE TABLE IMAGE_LOCATION(
	id int identity(1,1),
	location int,
	imagelink varchar(50),
	CONSTRAINT pk_IMAGE_LOCATION PRIMARY KEY(id),
	FOREIGN KEY(location) REFERENCES LOCATION(idlocal)
)

GO
CREATE TABLE REPORT_ERROR(
	id int identity(1,1),
	location int,
	errorname nvarchar(50),
	content nvarchar(1000),
	[status] int,
	CONSTRAINT pk_REPORT PRIMARY KEY(id),
	FOREIGN KEY(location) REFERENCES LOCATION(idlocal)
)

DROP TABLE ACCOUNT
DROP TABLE CATEGORY
DROP TABLE SOURCE_LINK
DROP TABLE PROVINCE
DROP TABLE LOCATION
DROP TABLE USER_LOCATION
DROP TABLE IMAGE_LOCATION
DROP TABLE REPORT_ERROR

--===========INSERT INTO ACCOUNT
INSERT INTO ACCOUNT VALUES('sinh','Yên Dũng Bắc Giang','Nguyễn Đức Sinh','sinh@gmail.com','12345','02/02/1992',423343634,0984242423,'System Admin')
INSERT INTO ACCOUNT VALUES('sinh01','Yên Dũng Bắc Giang','Nguyễn Đức Nam','sinh@gmail.com','12345','02/02/1992',423343634,0984242423,'Content Editor')
INSERT INTO ACCOUNT VALUES('sinh02','Yên Dũng Bắc Giang','Nguyễn Đức Sinh Nam','sinh@gmail.com','12345','02/02/1992',423343634,0984242423,'System Admin')
INSERT INTO ACCOUNT VALUES('sinh03','Yên Dũng Bắc Giang','Nguyễn Đức Hiểu','sinh@gmail.com','12345','02/02/1992',423343634,0984242423,'System Admin')
INSERT INTO ACCOUNT VALUES('sinh04','Yên Dũng Bắc Giang','Nguyễn Đức Diễn','sinh@gmail.com','12345','02/02/1992',423343634,0984242423,'System Admin')

-- ============INSERT INTO CATEGORY

INSERT INTO CATEGORY VALUES(N'Bãi biển')--id = 1
INSERT INTO CATEGORY VALUES(N'Đền chùa')--id = 2
INSERT INTO CATEGORY VALUES(N'Danh lam thắng cảnh')--id = 3
INSERT INTO CATEGORY VALUES(N'Di tích lịch sử')--id = 4
INSERT INTO CATEGORY VALUES(N'Vui chơi giải trí')--id = 5
INSERT INTO CATEGORY VALUES(N'Nhà nghỉ')--id = 6

--=========INSERT INTO MANAGE_LINK

INSERT INTO SOURCE_LINK VALUES(2,'https://www.facebook.com/','Tồn Tại')
INSERT INTO SOURCE_LINK VALUES(3,'https://www.youtube.com/','Tồn Tại')
INSERT INTO SOURCE_LINK VALUES(1,'https://www.w3school.com/','Tồn Tại')
INSERT INTO SOURCE_LINK VALUES(2,'https://www.facebook.com/','Tồn Tại')
INSERT INTO SOURCE_LINK VALUES(5,'https://www.facebook.com/','Tồn Tại')

--===========INSERT INTO PROVINCE

INSERT INTO PROVINCE VALUES(N'An Giang') INSERT INTO PROVINCE VALUES(N'Bà Rịa Vũng Tàu') INSERT INTO PROVINCE VALUES(N'Bắc Giang')
INSERT INTO PROVINCE VALUES(N'Bắc Kạn')  INSERT INTO PROVINCE VALUES(N'Bạc Liêu')        INSERT INTO PROVINCE VALUES(N'Bắc Ninh')
INSERT INTO PROVINCE VALUES(N'Bến Tre')  INSERT INTO PROVINCE VALUES(N'Bình Định')       INSERT INTO PROVINCE VALUES(N'Bình Dương')
INSERT INTO PROVINCE VALUES(N'Bình Phước')INSERT INTO PROVINCE VALUES(N'Bình Thuận')     INSERT INTO PROVINCE VALUES(N'Cà Mau')
INSERT INTO PROVINCE VALUES(N'Cần Thơ')   INSERT INTO PROVINCE VALUES(N'Cao Bằng')       INSERT INTO PROVINCE VALUES(N'Đà Nẵng')
INSERT INTO PROVINCE VALUES(N'Đắk Lắk')   INSERT INTO PROVINCE VALUES(N'Đăk Nông')       INSERT INTO PROVINCE VALUES(N'Điện Biên')
INSERT INTO PROVINCE VALUES(N'Đồng Nai')  INSERT INTO PROVINCE VALUES(N'Đồng Tháp')      INSERT INTO PROVINCE VALUES(N'Gia Lai')
INSERT INTO PROVINCE VALUES(N'Hà Giang')  INSERT INTO PROVINCE VALUES(N'Hà Nam')         INSERT INTO PROVINCE VALUES(N'Hà Nội')
INSERT INTO PROVINCE VALUES(N'Hà Tĩnh')   INSERT INTO PROVINCE VALUES(N'Hải Dương')      INSERT INTO PROVINCE VALUES(N'Hải Phòng')
INSERT INTO PROVINCE VALUES(N'Hậu Giang') INSERT INTO PROVINCE VALUES(N'Hòa Bình')       INSERT INTO PROVINCE VALUES(N'Hưng Yên')  
INSERT INTO PROVINCE VALUES(N'Khánh Hòa') INSERT INTO PROVINCE VALUES(N'Kiên Giang')     INSERT INTO PROVINCE VALUES(N'Kon Tum')
INSERT INTO PROVINCE VALUES(N'Lai Châu')  INSERT INTO PROVINCE VALUES(N'Lâm Đồng')       INSERT INTO PROVINCE VALUES(N'Lạng Sơn')
INSERT INTO PROVINCE VALUES(N'Lào Cai')   INSERT INTO PROVINCE VALUES(N'Long An')        INSERT INTO PROVINCE VALUES(N'Nam Định')
INSERT INTO PROVINCE VALUES(N'Nghệ An')   INSERT INTO PROVINCE VALUES(N'Ninh Bình')      INSERT INTO PROVINCE VALUES(N'Ninh Thuận')
INSERT INTO PROVINCE VALUES(N'Phú Thọ')   INSERT INTO PROVINCE VALUES(N'Phú Yên')        INSERT INTO PROVINCE VALUES(N'Quảng Bình')
INSERT INTO PROVINCE VALUES(N'Quảng Nam') INSERT INTO PROVINCE VALUES(N'Quảng Ngãi')     INSERT INTO PROVINCE VALUES(N'Quảng Ninh')
INSERT INTO PROVINCE VALUES(N'Quảng Trị') INSERT INTO PROVINCE VALUES(N'Sóc Trăng')      INSERT INTO PROVINCE VALUES(N'Sơn La')
INSERT INTO PROVINCE VALUES(N'Tây Ninh')  INSERT INTO PROVINCE VALUES(N'Thái Bình')      
INSERT INTO PROVINCE VALUES(N'Thanh Hóa') INSERT INTO PROVINCE VALUES(N'Thành Phố Hồ Chí Minh') INSERT INTO PROVINCE VALUES(N'Thừa Thiên Huế')
INSERT INTO PROVINCE VALUES(N'Thái Nguyên')INSERT INTO PROVINCE VALUES(N'Tiền Giang')    INSERT INTO PROVINCE VALUES(N'Trà Vinh')
INSERT INTO PROVINCE VALUES(N'Tuyên Quang')INSERT INTO PROVINCE VALUES(N'Vĩnh Long')
INSERT INTO PROVINCE VALUES(N'Vĩnh Phúc')INSERT INTO PROVINCE VALUES(N'Yên Bái')

SELECT * FROM PROVINCE
--===========INSERT INTO LOCATION

INSERT INTO LOCATION VALUES('Chùa một cột',2,2,'Ba Đình','Số 5 Ba Đình Hà Nội',0984743447,'N/A','Chưa có thông tin',2,'sinhndse02395@gmail.com')
INSERT INTO LOCATION VALUES('Chùa Bái Đính',2,4,'Ba Đình','Số 5 Ba Đình Hà Nội',0984743447,'N/A','Chưa có thông tin',3,'sinhndse02395@gmail.com')
INSERT INTO LOCATION VALUES('Hồ Gươm',3,12,'Ba Đình','Số 5 Ba Đình Hà Nội',0984743447,'N/A','Chưa có thông tin',5,'sinhndse02395@gmail.com')
INSERT INTO LOCATION VALUES('Royal city',5,53,'Ba Đình','Số 5 Ba Đình Hà Nội',0984743447,'N/A','Chưa có thông tin',1,'sinhndse02395@gmail.com')
INSERT INTO LOCATION VALUES('time city',5,23,'Ba Đình','Số 5 Ba Đình Hà Nội',0984743447,'N/A','Chưa có thông tin',4,'sinhndse02395@gmail.com')

select * from LOCATION
--===========INSERT INTO IMAGE_LOCATION

INSERT INTO IMAGE_LOCATION VALUES(2,'http://www.facebook.com')
INSERT INTO IMAGE_LOCATION VALUES(4,'http://www.facebook.com')
INSERT INTO IMAGE_LOCATION VALUES(3,'http://www.facebook.com')
INSERT INTO IMAGE_LOCATION VALUES(3,'http://www.facebook.com')
INSERT INTO IMAGE_LOCATION VALUES(5,'http://www.facebook.com')

--==========INSERT INTO REPORT_ERROR

INSERT INTO REPORT_ERROR VALUES(2,'Sai thông tin chùa một cột','thông tin về lịch sử bị sai',1)
INSERT INTO REPORT_ERROR VALUES(4,'Sai thông tin chùa một cột','thông tin về lịch sử bị sai',1)
INSERT INTO REPORT_ERROR VALUES(3,'Sai thông tin chùa một cột','thông tin về lịch sử bị sai',1)
INSERT INTO REPORT_ERROR VALUES(5,'Sai thông tin chùa bái đính','thông tin về lịch sử bị sai',1)
INSERT INTO REPORT_ERROR VALUES(2,'Sai thông tin hồ gươm','thông tin về lịch sử bị sai',2)
INSERT INTO REPORT_ERROR VALUES(2,'Sai thông tin hồ gươm','thông tin về lịch sử bị sai',2)
INSERT INTO REPORT_ERROR VALUES(2,'Sai thông tin hồ gươm','thông tin về lịch sử bị sai',2)

SELECT errorname,localname,A.category as [category],content,status
FROM (
	SELECT errorname,localname,b.category,content,status
	FROM LOCATION a,CATEGORY b,REPORT_ERROR c
	WHERE a.category = b.idcate AND c.location = a.idlocal )AS A
WHERE category LIKE '%vui%'

